﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Homework_8
{
    class Program
    {
        static void Main(string[] args)
        {
            var database = new StudentManagementSystemDatabase();
            database.CreateDatabase();

            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("Please select an option:");
                Console.WriteLine("1. Schools");
                Console.WriteLine("2. Classes");
                Console.WriteLine("3. Students");
                Console.WriteLine("4. Logs");
                Console.WriteLine("0. Exit");
                Console.Write("Option: ");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        SchoolsMenu(database);
                        break;
                    case "2":
                        ClassesMenu(database);
                        break;
                    case "3":
                        StudentsMenu(database);
                        break;
                    case "4":
                        LogsMenu(database);
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid option.");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void SchoolsMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("Schools Menu");
                Console.WriteLine("1. List schools");
                Console.WriteLine("2. Add school");
                Console.WriteLine("3. Update school");
                Console.WriteLine("4. Delete school");
                Console.WriteLine("0. Back");
                Console.Write("Option: ");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        var schools = database.GetSchools();
                        Console.WriteLine("Schools:");
                        foreach (var School in schools)
                        {
                            Console.WriteLine($"SchoolId: {School.SchoolId}, Name: {School.Name}");
                        }
                        break;
                    case "2":
                        Console.Write("School name: ");
                        string name = Console.ReadLine();
                        var school = new School { Name = name };
                        database.AddSchool(school);
                        Console.WriteLine("School added.");
                        database.LogOperation($"School {school.Name} added.");
                        break;
                    case "3":
                        Console.Write("School ID: ");
                        int schoolId = int.Parse(Console.ReadLine());
                        Console.Write("New school name: ");
                        name = Console.ReadLine();
                        school = new School { SchoolId = schoolId, Name = name };
                        database.UpdateSchool(school);
                        Console.WriteLine("School updated.");
                        database.LogOperation($"School {school.SchoolId} updated.");
                        break;
                    case "4":
                        Console.Write("School ID: ");
                        schoolId = int.Parse(Console.ReadLine());
                        database.DeleteSchool(schoolId);
                        Console.WriteLine("School deleted.");
                        database.LogOperation($"School {schoolId} deleted.");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid option.");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void ClassesMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("Classes Menu");
                Console.WriteLine("1. List classes by school ID");
                Console.WriteLine("2. Add class");
                Console.WriteLine("3. Update class");
                Console.WriteLine("4. Delete class");
                Console.WriteLine("0. Back");
                Console.Write("Option: ");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        Console.Write("School ID: ");
                        int schoolId = int.Parse(Console.ReadLine());
                        var classes = database.GetClassesBySchoolId(schoolId);
                        Console.WriteLine($"Classes in School {schoolId}:");
                        foreach (var @class in classes)
                        {
                            Console.WriteLine($"ClassId: {@class.ClassId}, Name: {@class.Name}");
                        }
                        break;
                    case "2":
                        Console.Write("School ID: ");
                        schoolId = int.Parse(Console.ReadLine());
                        Console.Write("Class name: ");
                        string name = Console.ReadLine();
                        var Class = new Class { SchoolId = schoolId, Name = name };
                        database.AddClass(Class);
                        Console.WriteLine("Class added.");
                        database.LogOperation($"Class {Class.Name} added.");
                        break;
                    case "3":
                        Console.Write("Class ID: ");
                        int classId = int.Parse(Console.ReadLine());
                        Console.Write("New class name: ");
                        name = Console.ReadLine();
                        Class = new Class { ClassId = classId, Name = name };
                        database.UpdateClass(Class);
                        Console.WriteLine("Class updated.");
                        database.LogOperation($"Class {Class.ClassId} updated.");
                        break;
                    case "4":
                        Console.Write("Class ID: ");
                        classId = int.Parse(Console.ReadLine());
                        database.DeleteClass(classId);
                        Console.WriteLine("Class deleted.");
                        database.LogOperation($"Class {classId} deleted.");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid option.");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void StudentsMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("Students Menu");
                Console.WriteLine("1. List students by class ID");
                Console.WriteLine("2. Add student");
                Console.WriteLine("3. Update student");
                Console.WriteLine("4. Delete student");
                Console.WriteLine("0. Back");
                Console.Write("Option: ");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        Console.Write("Class ID: ");
                        int classId = int.Parse(Console.ReadLine());
                        var students = database.GetStudentsByClassId(classId);
                        Console.WriteLine($"Students in Class {classId}:");
                        foreach (var Student in students)
                        {
                            Console.WriteLine($"StudentId: {Student.StudentId}, Name: {Student.Name}");
                        }
                        break;
                    case "2":
                        Console.Write("Class ID: ");
                        classId = int.Parse(Console.ReadLine());
                        Console.Write("Student name: ");
                        string name = Console.ReadLine();
                        var student = new Student { ClassId = classId, Name = name };
                        database.AddStudent(student);
                        Console.WriteLine("Student added.");
                        database.LogOperation($"Student {student.Name} added.");
                        break;
                    case "3":
                        Console.Write("Student ID: ");
                        int studentId = int.Parse(Console.ReadLine());
                        Console.Write("New student name: ");
                        name = Console.ReadLine();
                        student = new Student { StudentId = studentId, Name = name };
                        database.UpdateStudent(student);
                        Console.WriteLine("Student updated.");
                        database.LogOperation($"Student {student.StudentId} updated.");
                        break;
                    case "4":
                        Console.Write("Student ID: ");
                        studentId = int.Parse(Console.ReadLine());
                        database.DeleteStudent(studentId);
                        Console.WriteLine("Student deleted.");
                        database.LogOperation($"Student {studentId} deleted.");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid option.");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void LogsMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("Logs Menu");
                Console.WriteLine("1. List logs");
                Console.WriteLine("2. Clear logs");
                Console.WriteLine("0. Back");
                Console.Write("Option: ");
                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        var logs = database.GetLogs();
                        Console.WriteLine("Logs:");
                        foreach (var log in logs)
                        {
                            Console.WriteLine($"LogId: {log.LogId}, Operation: {log.Operation}, Timestamp: {log.Timestamp}");
                        }
                        break;
                    case "2":
                        database.ClearLogs();
                        Console.WriteLine("Logs cleared.");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("Invalid option.");
                        break;
                }

                Console.WriteLine();
            }
        }
    }

    public class StudentManagementSystemDatabase
    {
        private string connectionString = "Data Source=student_management_system.db;Version=3;";

        public void CreateDatabase()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string createSchoolsTableQuery = "CREATE TABLE IF NOT EXISTS Schools (SchoolId INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT)";
                string createClassesTableQuery = "CREATE TABLE IF NOT EXISTS Classes (ClassId INTEGER PRIMARY KEY AUTOINCREMENT, SchoolId INTEGER, Name TEXT)";
                string createStudentsTableQuery = "CREATE TABLE IF NOT EXISTS Students (StudentId INTEGER PRIMARY KEY AUTOINCREMENT, ClassId INTEGER, Name TEXT)";
                string createLogsTableQuery = "CREATE TABLE IF NOT EXISTS Logs (LogId INTEGER PRIMARY KEY AUTOINCREMENT, Operation TEXT, Timestamp TEXT)";

                using (var command = new SQLiteCommand(createSchoolsTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createClassesTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createStudentsTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                using (var command = new SQLiteCommand(createLogsTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<School> GetSchools()
        {
            List<School> schools = new List<School>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Schools";

                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int schoolId = Convert.ToInt32(reader["SchoolId"]);
                            string name = reader["Name"].ToString();
                            var school = new School { SchoolId = schoolId, Name = name };
                            schools.Add(school);
                        }
                    }
                }
            }

            return schools;
        }

        public void AddSchool(School school)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "INSERT INTO Schools (Name) VALUES (@Name)";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void UpdateSchool(School school)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "UPDATE Schools SET Name = @Name WHERE SchoolId = @SchoolId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.Parameters.AddWithValue("@SchoolId", school.SchoolId);
                    command.ExecuteNonQuery();
                }
            }
        }
        public void DeleteSchool(int schoolId)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "DELETE FROM Schools WHERE SchoolId = @SchoolId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@SchoolId", schoolId);
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<Class> GetClassesBySchoolId(int schoolId)
        {
            List<Class> classes = new List<Class>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Classes WHERE SchoolId = @SchoolId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@SchoolId", schoolId);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int classId = Convert.ToInt32(reader["ClassId"]);
                            string name = reader["Name"].ToString();
                            var Class = new Class { ClassId = classId, SchoolId = schoolId, Name = name };
                            classes.Add(Class);
                        }
                    }
                }
            }

            return classes;
        }

        public void AddClass(Class Class)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "INSERT INTO Classes (SchoolId, Name) VALUES (@SchoolId, @Name)";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@SchoolId", Class.SchoolId);
                    command.Parameters.AddWithValue("@Name", Class.Name);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void UpdateClass(Class Class)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "UPDATE Classes SET Name = @Name WHERE ClassId = @ClassId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", Class.Name);
                    command.Parameters.AddWithValue("@ClassId", Class.ClassId);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteClass(int classId)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "DELETE FROM Classes WHERE ClassId = @ClassId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ClassId", classId);
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<Student> GetStudentsByClassId(int classId)
        {
            List<Student> students = new List<Student>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Students WHERE ClassId = @ClassId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ClassId", classId);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int studentId = Convert.ToInt32(reader["StudentId"]);
                            string name = reader["Name"].ToString();
                            var student = new Student { StudentId = studentId, ClassId = classId, Name = name };
                            students.Add(student);
                        }
                    }
                }
            }

            return students;
        }

        public void AddStudent(Student student)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "INSERT INTO Students (ClassId, Name) VALUES (@ClassId, @Name)";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ClassId", student.ClassId);
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void UpdateStudent(Student student)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "UPDATE Students SET Name = @Name WHERE StudentId = @StudentId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@StudentId", student.StudentId);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteStudent(int studentId)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "DELETE FROM Students WHERE StudentId = @StudentId";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@StudentId", studentId);
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<Log> GetLogs()
        {
            List<Log> logs = new List<Log>();

            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Logs";

                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int logId = Convert.ToInt32(reader["LogId"]);
                            string operation = reader["Operation"].ToString();
                            string timestamp = reader["Timestamp"].ToString();
                            var log = new Log { LogId = logId, Operation = operation, Timestamp = timestamp };
                            logs.Add(log);
                        }
                    }
                }
            }

            return logs;
        }

        public void LogOperation(string operation)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "INSERT INTO Logs (Operation, Timestamp) VALUES (@Operation, @Timestamp)";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Operation", operation);
                    command.Parameters.AddWithValue("@Timestamp", DateTime.Now.ToString());
                    command.ExecuteNonQuery();
                }
            }
        }

        public void ClearLogs()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string query = "DELETE FROM Logs";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
    }

    public class School
    {
        public int SchoolId { get; set; }
        public string Name { get; set; }
    }

    public class Class
    {
        public int ClassId { get; set; }
        public int SchoolId { get; set; }
        public string Name { get; set; }
    }

    public class Student
    {
        public int StudentId { get; set; }
        public int ClassId { get; set; }
        public string Name { get; set; }
    }

    public class Log
    {
        public int LogId { get; set; }
        public string Operation { get; set; }
        public string Timestamp { get; set; }
    }
}
